/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.googlecode.jsimplemarket.model;

import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;

/**
 *
 * @author Alexandru.Codescu
 */
@Entity
@Table(name = "spatii_comerciale")
@NamedNativeQueries (
        {
            @NamedNativeQuery(name = "queryStatisticiSpatiuComercialByData", query = 
"select r.id as randul, sc.id, sc.cod as cod_spatiu, subq_ai.stare as stare, nssc.css_class,\n" +
//"select r.id as randul, sc.id, sc.cod as cod_spatiu, nssc.denumire as stare, nssc.css_class,\n" +            
"	ifnull(subq_ai.inchiriere_id, subq_inch.inchiriere_id) as inchiriere_id,\n" +
"	ifnull(f_ai.denumire, f_inch.denumire) as firma,\n" +
"	ifnull(subqa_ai.absent, subqa_inch.absent) as absent,\n" +
"	ifnull(subq_ai.di, subq_inch.di) as data_inceput,\n" +
"	sql1.id as izid, sql1.data_inceput as izdi, sql1.tid as tid, sql1.valoare as tval, sql1.css_class as tcss\n" +
"from spatii_comerciale sc \n" +
"left join n_stari_spatiu_comercial nssc on nssc.id = sc.id_stare_spatiu_comercial\n" +
"inner join randuri r on sc.id_rand = r.id\n" +
"left join (\n" +
"select i.id_spatiu_comercial, max(i.id) as inchiriere_id, max(i.data_inceput) as di, t.denumire_stare as stare \n" +
"from inchirieri i \n" +
"inner join creante c on i.id = c.id_inchiriere\n" +
"inner join taxe t on t.id = c.id_taxa\n" +
"where (t.id_tip_taxa = 2 or t.id_tip_taxa = 3 or t.id_tip_taxa = 4) and i.data_inceput >= ?2 and i.data_inceput <= ?1\n" +
"group by i.id_spatiu_comercial) as subq_ai on subq_ai.id_spatiu_comercial = sc.id\n" +
"left join inchirieri inch_ai on inch_ai.id = subq_ai.inchiriere_id\n" +
"left join firme f_ai on f_ai.id = inch_ai.id_firma\n" +
"left join (\n" +
"	select a.id_spatiu_comercial, a.id_firma, count(*) absent\n" +
"	from absente a \n" +
"	where a.data_absenta >= ?2\n" +
"	group by a.id_spatiu_comercial, a.id_firma) subqa_ai on subqa_ai.id_firma = inch_ai.id_firma and subqa_ai.id_spatiu_comercial = inch_ai.id_spatiu_comercial\n" +
"left join (\n" +
"select i.id_spatiu_comercial, max(i.id) as inchiriere_id, max(i.data_inceput) as di \n" +
"from inchirieri i \n" +
"where i.data_inceput <= ?3\n" +
"group by i.id_spatiu_comercial) as subq_inch on subq_inch.id_spatiu_comercial = sc.id\n" +
"left join inchirieri inch on inch.id = subq_inch.inchiriere_id\n" +
"left join firme f_inch on f_inch.id = inch.id_firma\n" +
"left join (\n" +
"	select a.id_spatiu_comercial, a.id_firma, count(*) absent\n" +
"	from absente a \n" +
"	where a.data_absenta >= ?4\n" +
"	group by a.id_spatiu_comercial, a.id_firma) subqa_inch on subqa_inch.id_firma = inch.id_firma and subqa_inch.id_spatiu_comercial = inch.id_spatiu_comercial\n" +
"left join (\n" +
"	select i.id_spatiu_comercial, i.id, i.data_inceput, i.id_firma, t.id as tid, t.valoare, t.css_class\n" +
"	from inchirieri i \n" +
"	inner join creante c on i.id = c.id_inchiriere \n" +
"	inner join taxe t on t.id = c.id_taxa \n" +
"	where t.id_tip_taxa = 1 and i.data_inceput between ?5 and ?6\n" +
"	group by i.id_spatiu_comercial\n" +
"	order by i.data_inceput desc) as sql1 on sc.id=sql1.id_spatiu_comercial\n" +
"order by cod_spatiu")
        }
)
public class SpatiuComercial extends AbstractEntitate {
    private NTipSpatiuComercial tipSpatiuComercial;
    private NStareSpatiuComercial stareSpatiuComercial;
    private String cod;
    private List<ObservatieSpatiuComercial> observatii;
    private List<FisierAtasatSpatiuComercial> fisiereAtasateSpatiuComercial;
    private List<Inchiriere> inchiriere;
    private Rand rand;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_RAND")
    public Rand getRand() {
    	return rand;
    }
    
    public void setRand(Rand rand) {
    	this.rand = rand;
    }
    
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_TIP_SPATIU_COMERCIAL")
    public NTipSpatiuComercial getTipSpatiuComercial() {
        return tipSpatiuComercial;
    }

    public void setTipSpatiuComercial(NTipSpatiuComercial tipSpatiuComercial) {
        this.tipSpatiuComercial = tipSpatiuComercial;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_STARE_SPATIU_COMERCIAL")
    public NStareSpatiuComercial getStareSpatiuComercial() {
        return stareSpatiuComercial;
    }

    public void setStareSpatiuComercial(NStareSpatiuComercial stareSpatiuComercial) {
        this.stareSpatiuComercial = stareSpatiuComercial;
    }

    @Column(name = "COD", nullable = false, length = 5)
    public String getCod() {
        return cod;
    }

    public void setCod(String cod) {
        this.cod = cod;
    }

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_SPATIU_COMERCIAL")
    public List<ObservatieSpatiuComercial> getObservatii() {
        return observatii;
    }

    public void setObservatii(List<ObservatieSpatiuComercial> observatii) {
        this.observatii = observatii;
    }

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_SPATIU_COMERCIAL")
    public List<FisierAtasatSpatiuComercial> getFisiereAtasateSpatiuComercial() {
        return fisiereAtasateSpatiuComercial;
    }

    public void setFisiereAtasateSpatiuComercial(List<FisierAtasatSpatiuComercial> fisiereAtasateSpatiuComercial) {
        this.fisiereAtasateSpatiuComercial = fisiereAtasateSpatiuComercial;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "spatiuComercial")
    public List<Inchiriere> getInchiriere() {
        return inchiriere;
    }

    public void setInchiriere(List<Inchiriere> inchiriere) {
        this.inchiriere = inchiriere;
    }
}
